
IF OBJECT_ID('yp_getid') IS NOT NULL
		DROP PROC dbo.yp_getid
GO


CREATE PROC dbo.yp_getid
		-- @pID和@pTableName至少要一个
		-- 如果没有呢?用ID=0的
		@pID		INT = 0,
		@pTableName VARCHAR(200) = '',
		@pQty		INT = 1,			-- 获取多少个		
		@tResult	INT OUTPUT
AS
BEGIN
		SET NOCOUNT ON
/**
		获取ID
		2019-11-01 12:51:41
		2020-01-24 10:44:53 改为y*
*/

		IF ISNULL(@pID, 0) <= 0 SET @pID = 0
		SET @pTableName = ISNULL(@pTableName, '') 
		IF ISNULL(@pQty, 0) <= 0 SET @pQty = 1

		IF @pTableName <> ''
		BEGIN
				UPDATE dbo.yt_id SET xCurrentValue += @pQty, @tResult = xCurrentValue + 1 WHERE xTableName = @pTableName
				IF @tResult IS NULL	-- 说明没有更新成功, 原因可能是:没有分配id
				BEGIN
						-- INSERT INTO dbo.ct_id (sID, xName, xValue,sCreateTime, sLastUpdate, sVersion) SELECT MAX(sID) + 1, @pTableName, 1, GETDATE(),  GETDATE(),1 FROM dbo.ct_id AS m (NOLOCK)
						UPDATE dbo.yt_id SET xTableName = @pTableName, sLastUpdate = GETDATE(), sStatus = 3, sVersion += 1 WHERE xTableName = ''
						-- 还没有的话,说明是这个表一行数据都还没有
						-- 这种情况基本上不会出现, 出现的话就可以肯定是哪里出错了导致所有预定义的ID范围都被占用了
						/**
						IF @@ROWCOUNT = 0
						BEGIN
								INSERT INTO dbo.ct_id (sID, xName, xValue,  sCreateTime, sLastUpdate, sVersion) SELECT 1, @pTableName, 1, GETDATE(),  GETDATE(),1
						END
						**/
				END
		END

		-- 按ID来取,或者按表来取但是没有取出来
		IF @pID > 0 OR (@pTableName <> '' AND @tResult IS NULL)
		BEGIN
				-- UPDATE dbo.ct_id SET xValue += @pQty , @tResult = xValue + 1 WHERE sID = @pID
				UPDATE dbo.yt_id SET xCurrentValue += @pQty, @tResult = xCurrentValue + 1 WHERE sID = @pID
				IF @tResult IS NULL
				BEGIN
						-- INSERT INTO dbo.ct_id (sID, xName, xValue ,sCreateTime, sLastUpdate, sVersion) SELECT @pID, '', 1,GETDATE(),  GETDATE(),1
						INSERT INTO dbo.yt_id(sID,xTableName, xBeginValue, xEndValue, xCurrentValue, sCreateTime, sLastUpdate, sStatus, sVersion)
						VALUES(@pID, '', 0, 2100000000, 0, GETDATE(), GETDATE(), 3, 1)
				END
		END

		IF @tResult IS NULL SET @tResult = 1
END

GO


--DECLARE @tResult INT;
--EXEC dbo.cp_getid @pID = 0, -- int
--                  @pTableName = 'dt_tablesize', -- varchar(200)
--                  @pQty = 1, -- int
--                  @tResult = @tResult OUTPUT -- int
--PRINT @tResult
